package com.itjeffrey.autocode.common;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itjeffrey.autocode.constant.SysConstant;
import com.itjeffrey.autocode.entity.LoginInfoEntity;
import com.itjeffrey.autocode.enums.ExceptionEnum;
import com.itjeffrey.autocode.exception.ACException;
import com.itjeffrey.autocode.mapper.LoginInfoMapper;
import com.itjeffrey.autocode.bo.LoginInfoBO;
import com.itjeffrey.autocode.bo.req.DbLinkBO;
import com.itjeffrey.autocode.bo.resp.ColumnInfo;
import com.itjeffrey.autocode.util.DBUtil;
import com.itjeffrey.autocode.websocket.BaseSocketServer;
import com.itjeffrey.autocode.websocket.WebSocketServerContainer;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;

import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 基础通用类
 *
 * @From: Jeffrey
 * @Date: 2020/11/8
 */
@Component
@Slf4j
public class BaseCommon {

    @Resource
    private LoginInfoMapper loginInfoMapper;

    @Resource
    private DBUtil dbUtil;

    //维护用户登录token，key为登录名，value为访问Token
    public static Map<String, String> tokenMap = new ConcurrentHashMap<>();

    //存放连接的容器，key为数据库名，value为连接对象
    public static Map<String, Connection> connMap = new ConcurrentHashMap<>();

    //维护用户登录信息，避免每次请求查询数据库获取登录信息, key为登录名，value为对应的登录用户信息
    public static Map<String, LoginInfoBO> loginInfoMap = new ConcurrentHashMap<>();

    /**
     * 容器加载完成时执行此方法，初始化loginInfoMap，以后每次变更登录表信息时均需调用该方法实现初始化用户登录信息
     *
     * @return
     */
    @PostConstruct
    public void initLoginInfo() {
        List<LoginInfoEntity> loginInfoEntities = loginInfoMapper.selectList(new QueryWrapper<>());
        if (CollectionUtils.isEmpty(loginInfoEntities)) {
            throw new ACException(ExceptionEnum.LOGIN_INFO_NOT_EXISTS);
        }
        loginInfoEntities.forEach(t -> {
            LoginInfoBO loginInfoBO = new LoginInfoBO();
            BeanUtils.copyProperties(t, loginInfoBO);
            loginInfoMap.put(t.getLoginName(), loginInfoBO);
        });
        log.info("用户登录信息初始化完成！用户登录信息统计：[{}]", loginInfoMap.size());
    }

    /**
     * 校验数据源信息
     *
     * @param dbLinkBo
     */
    public void checkDbSource(DbLinkBO dbLinkBo) {
        if (StringUtils.isEmpty(dbLinkBo.getDbDriver()) || StringUtils.isEmpty(dbLinkBo.getIp()) || StringUtils
                .isEmpty(dbLinkBo.getPort()) ||
                StringUtils.isEmpty(dbLinkBo.getDatabase()) || StringUtils.isEmpty(dbLinkBo.getUsername()) ||
                StringUtils.isEmpty(dbLinkBo.getPassword())) {
            throw new ACException(ExceptionEnum.PARAM_EXISTS_NULL);
        }
    }

    /**
     * 获取HikariDataSource数据源
     *
     * @param dbLinkBo
     * @return
     */
    public DataSource getDataSource(DbLinkBO dbLinkBo) {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(dbLinkBo.getDbDriver());
        String url;
        if (SysConstant.MYSQL_DATABASE_DRIVER.equals(dbLinkBo.getDbDriver())) {
            url = "jdbc:mysql://" + dbLinkBo.getIp() + SysConstant.SPECIAL_CHAR_15 + dbLinkBo.getPort() + SysConstant
                    .SPECIAL_CHAR_16 + dbLinkBo.getDatabase() +
                    "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT";
        } else {
            log.error("Oracle数据库获取失败！");
            throw new ACException(ExceptionEnum.INNER_SERVICE_ERROR_WITH_REASON, "Oracle数据库获取失败！");
        }
        hikariDataSource.setJdbcUrl(url);
        hikariDataSource.setUsername(dbLinkBo.getUsername());
        hikariDataSource.setPassword(dbLinkBo.getPassword());
        return hikariDataSource;
    }

    /**
     * 获取最新连接对象，若当前连接在connMap中不存在则put进去，若存在则关闭当前连接返回connMap中的连接
     *
     * @param connection 当前成功连接的connection对象
     * @param dbName     数据库名
     */
    public Connection getNewestConn(Connection connection, String dbName) throws ACException{
        //connMap获取连接
        Connection conn = getConnection(dbName);
        //没有则存储
        if (conn == null) {
            connMap.put(dbName, connection);
            //websocket发送消息给客户端
            String ip = "127.0.0.1"; //TODO
            try {
                BaseSocketServer webSocketServer = WebSocketServerContainer.getSocketServer(ip, "WebSocketServer");
                Assert.notNull(webSocketServer, "webSocketServer require not null!");
                webSocketServer.sendMessage(JSON.toJSONString(Result.ok(connMap.size())));
            } catch (IOException e) {
                log.error("connection连接数发送客户端失败！");
                e.printStackTrace();
            }
            log.info("connection连接数[{}]成功发送客户端！", connMap.size());
            return connection;
        } else {
            close(connection, null, null);
            return conn;
        }
    }

    /**
     * 根据key从connMap获取连接，存在则返回不存在则返回null
     *
     * @param key 数据库名
     * @return
     */
    public Connection getConnection(String key) throws ACException{
        Connection connection = null;
        if (isExistsConn(key)) { //connMap存在key,则直接返回对应的连接
            connection = connMap.get(key);
            if (connection == null) {
                throw new ACException(ExceptionEnum.DB_LINK_GET_FAILED);
            }
        }
        return connection;
    }

    /**
     * 校验指定数据库名所对应的连接是否存在于connMap中
     *
     * @param key
     * @return
     */
    public boolean isExistsConn(String key) {
        if (!connMap.containsKey(key)) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 根据key从connMap中释放连接，并将key从connMap中移除
     *
     * @param key 数据库名
     */
    public void freeConnection(String key) {
        if (isExistsConn(key)) {
            Connection connection = connMap.get(key);
            //关闭连接
            close(connection, null, null);
            connMap.remove(key);
            //发送消息给客户端
            String ip = "127.0.0.1"; //TODO
            try {
                BaseSocketServer webSocketServer = WebSocketServerContainer.getSocketServer(ip, "WebSocketServer");
                Assert.notNull(webSocketServer, "webSocketServer require not null!");
                webSocketServer.sendMessage(JSON.toJSONString(Result.ok(connMap.size())));
            } catch (IOException e) {
                log.error("connection连接数发送客户端失败！");
                e.printStackTrace();
            }
            log.info("connection连接数[{}]成功发送客户端！", connMap.size());
        }
    }


    /**
     * close释放资源
     *
     * @param rs
     */
    public void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 根据连接对象和数据库名称获取表名列表
     *
     * @param connection
     * @param dbName
     * @return
     * @throws SQLException
     */
    public List<String> getTableNamesByConn(Connection connection, String dbName) throws SQLException{
        List<String> tableNames = new ArrayList<>();
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            rs = metaData.getTables(dbName, null, null, null);
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            close(null, null, rs);
        }
        return tableNames;
    }

    /**
     * 通过数据库名和表名获取Mysql字段信息
     *
     * @param database
     * @param tableName
     * @Param flag 字段名称是否驼峰命名
     * @return
     */
    public List<ColumnInfo> getMysqlColumnInfoByConn(String database, String tableName, boolean flag) {
        List<ColumnInfo> columnInfos = new ArrayList<>();
        Connection connection = getConnection(database);
        if (connection == null) {
            throw new ACException(ExceptionEnum.CONNECTION_NOT_FOUND);
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE,COLUMN_KEY from " +
                    "information_schema.columns where table_name='" + tableName + "'");
            rs = ps.executeQuery();
            while (rs.next()) {
                ColumnInfo columnInfo = new ColumnInfo();
                String column_name = rs.getString("COLUMN_NAME");
                if(flag){
                    columnInfo.setColumnName(dbUtil.camelNameForColumn(column_name));
                }else {
                    columnInfo.setColumnName(column_name);
                }
                String data_type = rs.getString("DATA_TYPE").toUpperCase();
                columnInfo.setColumnType(dbUtil.columnToJavaClass(data_type));
                columnInfo.setColumnNote(rs.getString("COLUMN_COMMENT"));
                columnInfo.setColumnKey(rs.getString("COLUMN_KEY"));
                columnInfos.add(columnInfo);
            }
        } catch (SQLException e) {
            log.error("获取Mysql字段信息出错！");
            e.printStackTrace();
        } finally {
            close(null, ps, rs);
        }

        return columnInfos;
    }

    /**
     * 根据数据库名和表名获取字段信息
     *
     * @param database
     * @param tableName
     * @return
     */
    public List<ColumnInfo> getColumnInfoByConn(String database, String tableName) {
        Connection connection = getConnection(database);
        if (connection == null) {
            throw new ACException(ExceptionEnum.CONNECTION_NOT_FOUND);
        }
        List<ColumnInfo> columnNames = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("SELECT * FROM " + tableName);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount(); //字段数
            for (int i = 1; i <= columnCount; i++) {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnName(metaData.getColumnName(i));
                columnInfo.setColumnType(metaData.getColumnTypeName(i));
                columnInfo.setColumnNote(metaData.getColumnLabel(i));
                columnNames.add(columnInfo);
            }
        } catch (SQLException e) {
            log.error("获取字段信息出错！");
            e.printStackTrace();
        } finally {
            close(null, ps, rs);
        }
        return columnNames;
    }

}
